﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Xml;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace ReportService
{
    /// <summary>
    /// Summary description for MonthOverviewShippingMethodGroep7
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class MonthOverviewShippingMethodGroep7 : AbstractWebservice.AbstractService
    {
        private List<String> varList = new List<String>();

        ///
        /// <summary>Gets the dataset containing the reportdata</summary>
        /// <param name="dataSet">values of the components</param>
        ///
        [WebMethod]
        public override DataSet getDataset(DataSet dataSet)
        {
            //get reportlevel from dataset
            DataTable levelTable = dataSet.Tables[1];
            int level = Int32.Parse(levelTable.Rows[0][levelTable.Columns[0]].ToString());
            
            //get passed values
            DataTable dataTable = dataSet.Tables[2];
            int fromMonth = Int32.Parse(dataTable.Rows[0][dataTable.Columns[0]].ToString());
            int fromYear = Int32.Parse(dataTable.Rows[1][dataTable.Columns[0]].ToString());
            int toMonth = Int32.Parse(dataTable.Rows[2][dataTable.Columns[0]].ToString());
            int toYear = Int32.Parse(dataTable.Rows[3][dataTable.Columns[0]].ToString());

            //create from / to-date
            DateTime fromDate = new DateTime(fromYear, fromMonth, 1);
            DateTime toDate = new DateTime(toYear, toMonth, 1);

            DataSet returnDataSet = new DataSet();

            //Wouters connectionString
            String connectionString = @"Data Source=WOUTERDESKTOP\SQLEXPRESS; Initial Catalog=AdventureWorks; Integrated Security=True;";

            // queries for different 
            String lvl1Query = "SELECT shipMethod.Name,"
                                + " YEAR(sales.OrderDate) AS year,"
                                + " MONTH(sales.OrderDate) AS month,"
                                + " COUNT(sales.SalesOrderID) AS total"
                                + " FROM Sales.SalesOrderHeader as sales"
                                + " JOIN Purchasing.ShipMethod AS shipMethod ON sales.ShipMethodID = shipMethod.ShipMethodID"
                                + " WHERE sales.OrderDate >= '" + fromDate.ToString() + "' AND sales.OrderDate <= '" + toDate.ToString() + "'"
                                + " GROUP BY YEAR(sales.OrderDate), MONTH(sales.OrderDate), shipMethod.Name"
                                + " ORDER BY year, month, shipMethod.Name";

            String lvl2Query = "SELECT shipMethod.Name,"
                                + " YEAR(sales.OrderDate) AS year,"
                                + " MONTH(sales.OrderDate) AS month"
                                //+ " COUNT(sales.SalesOrderID) AS total"
                                + " FROM Sales.SalesOrderHeader as sales"
                                + " JOIN Purchasing.ShipMethod AS shipMethod ON sales.ShipMethodID = shipMethod.ShipMethodID"
                                + " WHERE sales.OrderDate >= '" + fromDate.ToString() + "' AND sales.OrderDate <= '" + toDate.ToString() + "'"
                                + " GROUP BY YEAR(sales.OrderDate), MONTH(sales.OrderDate), shipMethod.Name"
                                + " ORDER BY year, month, shipMethod.Name";
           
            //get query for specific level
            String query;
            switch (level)
            {
                default:
                    query = lvl1Query;
                    break;
                case 1:
                    query = lvl1Query;
                    break;
                case 2:
                    query = lvl2Query;
                    break;
             
            }

            //connect to the DB
            SqlConnection conn = new SqlConnection();
            SqlDataAdapter adapter = new SqlDataAdapter();
            //set connectionstring
            conn.ConnectionString = connectionString;
            //execute query
            adapter = new SqlDataAdapter(query, conn);
            //get results
            adapter.Fill(returnDataSet);
            //close connection
            conn.Close();

            //return database results
            return returnDataSet;
        }

        /// <summary>
        /// Gets the required input-components for the report
        /// </summary>
        /// <returns></returns>
        [WebMethod]
        public override DataSet getComponents()
        {
            XmlDocument xDoc = new XmlDocument();
            //create xml root
            XmlElement components = xDoc.CreateElement("components");
            xDoc.AppendChild(components);
           
            //add 1st component, being the month for the from-date
            XmlElement component1 = xDoc.CreateElement("component");
            components.AppendChild(component1);

            XmlAttribute label1 = xDoc.CreateAttribute("label");
            label1.Value = "Select from-month";
            component1.Attributes.Append(label1);

            XmlAttribute type1 = xDoc.CreateAttribute("type");
            type1.Value = "list";
            component1.Attributes.Append(type1);
            // Add items 
            for (int i = 1; i < 13; i++)
            {
                XmlElement item = xDoc.CreateElement("item");
                component1.AppendChild(item);

                XmlAttribute name = xDoc.CreateAttribute("name");
                name.Value = i.ToString();
                XmlAttribute value = xDoc.CreateAttribute("value");
                value.Value = i.ToString();
                item.Attributes.Append(name);
                item.Attributes.Append(value);
            }

            //add 2nd component, being the year for the from-date
            XmlElement component2 = xDoc.CreateElement("component");
            components.AppendChild(component2);

            XmlAttribute label2 = xDoc.CreateAttribute("label");
            label2.Value = "Select from-year";
            component2.Attributes.Append(label2);

            XmlAttribute type2 = xDoc.CreateAttribute("type");
            type2.Value = "list";
            component2.Attributes.Append(type2);
            // Add items 
            for (int i = 2000; i < 2006; i++)
            {
                XmlElement item = xDoc.CreateElement("item");
                component2.AppendChild(item);

                XmlAttribute name = xDoc.CreateAttribute("name");
                name.Value = i.ToString();
                XmlAttribute value = xDoc.CreateAttribute("value");
                value.Value = i.ToString();
                item.Attributes.Append(name);
                item.Attributes.Append(value);
            }


            //add 3rd component, being a list for the month for the to-date
            XmlElement component3 = xDoc.CreateElement("component");
            components.AppendChild(component3);

            XmlAttribute label3 = xDoc.CreateAttribute("label");
            label3.Value = "Select to-month";
            component3.Attributes.Append(label3);

            XmlAttribute type3 = xDoc.CreateAttribute("type");
            type3.Value = "list";
            component3.Attributes.Append(type3);
            // Add items 
            for (int i = 1; i < 13; i++)
            {
                XmlElement item = xDoc.CreateElement("item");
                component3.AppendChild(item);

                XmlAttribute name = xDoc.CreateAttribute("name");
                name.Value = i.ToString();
                XmlAttribute value = xDoc.CreateAttribute("value");
                value.Value = i.ToString();
                item.Attributes.Append(name);
                item.Attributes.Append(value);
            }

            //add 4rd component, being a list for the year for the to-date
            XmlElement component4 = xDoc.CreateElement("component");
            components.AppendChild(component4);

            XmlAttribute label4 = xDoc.CreateAttribute("label");
            label4.Value = "Select to-year";
            component4.Attributes.Append(label4);

            XmlAttribute type4 = xDoc.CreateAttribute("type");
            type4.Value = "list";
            component4.Attributes.Append(type4);
            // Add items.
            for (int i = 2000; i < 2006; i++)
            {
                XmlElement item = xDoc.CreateElement("item");
                component4.AppendChild(item);

                XmlAttribute name = xDoc.CreateAttribute("name");
                name.Value = i.ToString();
                XmlAttribute value = xDoc.CreateAttribute("value");
                value.Value = i.ToString();
                item.Attributes.Append(name);
                item.Attributes.Append(value);
            }

            //add xml to dataset.
            DataSet returnDataSet = new DataSet();
            returnDataSet.ReadXml(new XmlNodeReader(xDoc));

            //try to save it for debugging purposes.
            try
            {
                xDoc.Save(@"D:\getComponents.xml");
            }
            catch
            {

            } 
            // return the dataset containing the components.
            return returnDataSet;
        }

    }
}
